﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BiffenService.Model;
using System.Data.SqlClient;

namespace BiffenService.Db
{
    public class PriceCategoryDb : IPriceCategoryDb
    {
        private string table = "bif_price_category";

        /// <summary>
        /// A metohed to return a specific price category by id in the database
        /// </summary>
        /// <param name="id">int id, the id of the price category to be found</param>
        /// <returns>PriceCategory p, the price category found</returns>
        public PriceCategory GetPriceCategory(int id)
        {
            string sql = "SELECT * FROM " + table + " WHERE Id=@Id";
            SqlCommand cmd = DbClient.GetDbCommand(sql);
            cmd.Parameters.AddWithValue("@Id", id);

            SqlDataReader r = cmd.ExecuteReader();

            PriceCategory p = null;
            while (r.Read())
            {
                string name = r["Name"].ToString();
                decimal price = decimal.Parse(r["Price"].ToString());

                p = new PriceCategory(id, name, price);
            }
            DbClient.Close();

            return p;
        }

        /// <summary>
        /// Method to find all price categories
        /// </summary>
        /// <returns>prices, a list of price categories found in the database</returns>
        public List<PriceCategory> GetAllPriceCategories()
        {
            string sql = "SELECT * FROM " + table + " ORDER BY Name ASC";
            SqlCommand cmd = DbClient.GetDbCommand(sql);
            List<PriceCategory> prices = new List<PriceCategory>();

            SqlDataReader r = cmd.ExecuteReader();

            while (r.Read())
            {
                int id = int.Parse(r["Id"].ToString());
                string name  = r["Name"].ToString();
                decimal price = decimal.Parse(r["Price"].ToString());

                prices.Add(new PriceCategory(id, name, price));
            }

            DbClient.Close();

            if (prices.Count > 0)
            {
                return prices;
            }
            return null;
        }
    }
}
